package com.xbniao.gcfs.common.util.ExportExcel;

import com.xbniao.gcfs.common.constant.GcfsConstant;
import com.xbniao.gcfs.common.enums.GcfsCommodityBrandTypeEnums;
import com.xbniao.gcfs.common.response.model.CommodityExportModel;
import com.xbniao.gcfs.dto.GcfsCommodity;
import com.xbniao.gcfs.dto.GcfsCommodityClearCustom;
import com.xbniao.gcfs.dto.GcfsCommodityCustom;
import com.xbniao.gcfs.dto.GcfsCommodityRelationshipEbay;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author hanchunli
 * @version V1.0
 * @Title:
 * @Package com.xbniao.gcfs.service.impl
 * @date 2017/9/23 15:51
 * @Description: 导出商品信息
 */
public class ExportCommodity {

    public static HSSFWorkbook createExcel(List<CommodityExportModel> commoditys){
        HSSFWorkbook workbook = new HSSFWorkbook();
        try{
            HSSFSheet sheet = workbook.createSheet("XBN产品信息");
            sheet.setDisplayGridlines(false);
            sheet.autoSizeColumn(1);
            // 设置单元格样式
            HSSFCellStyle style = setCellStyle(workbook,1);
            HSSFCellStyle style1 = setCellStyle(workbook,2);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 18));
            HSSFRow h_row = sheet.createRow(0);
            h_row.setHeight((short) 500);
            HSSFCell cell;
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue("清关信息—待补充");
            cell = h_row.createCell(6);
            cell.setCellStyle(style1);
            cell.setCellValue("产品信息");
            List<String> commodityHead = commodityHead();
            int row = 1;
            h_row = sheet.createRow(row);
            setHead(commodityHead,h_row,style);
            if(commoditys !=null){
                for(CommodityExportModel commodity : commoditys){
                    int len = commodity.getClearCustoms().size();
                    for(int j=0;j<len;j++){
                        row = row + 1;
                        h_row = sheet.createRow(row);
                        h_row.setHeight((short)500);
                        GcfsCommodityClearCustom custom = commodity.getClearCustoms().get(j);
                        cell = h_row.createCell(0);
                        cell.setCellStyle(style1);

                        cell.setCellValue(custom.getDestinationCountry());
                        cell = h_row.createCell(1);
                        cell.setCellStyle(style1);
                        cell.setCellValue(custom.getCustomHsCode());
                        cell = h_row.createCell(2);
                        cell.setCellStyle(style1);
                        cell.setCellValue("");

                        cell = h_row.createCell(3);
                        cell.setCellStyle(style1);
                        if(custom.getCustomTaxRate() != null){
                            cell.setCellValue(custom.getCustomTaxRate());
                        }
                        cell = h_row.createCell(4);
                        cell.setCellStyle(style1);
                        if(custom.getVatGstTaxRate() != null){
                            cell.setCellValue(custom.getVatGstTaxRate());
                        }
                        cell = h_row.createCell(5);
                        cell.setCellStyle(style1);
                        cell = h_row.createCell(6);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getCommodityCode());
                        cell = h_row.createCell(7);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getHsCode());
                        cell = h_row.createCell(8);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getMerchantSku());
                        cell = h_row.createCell(9);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getApplyNameZh());
                        cell = h_row.createCell(10);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getApplyNameCn());
                        cell = h_row.createCell(11);
                        cell.setCellStyle(style1);
                        cell.setCellValue(GcfsCommodityBrandTypeEnums.getNameByCode(commodity.getBrandType()));
                        cell = h_row.createCell(12);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getBrand());
                        cell = h_row.createCell(13);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getSpecsModel());
                        cell = h_row.createCell(14);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getMaterial());
                        cell = h_row.createCell(15);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getPurpose());
                        cell = h_row.createCell(16);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getMerchantPhoto());
                        cell = h_row.createCell(17);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getCommodityInfoUrl());
                        cell = h_row.createCell(18);
                        cell.setCellStyle(style1);
                        cell.setCellValue(commodity.getUserNote());
                    }
                }
                for(int i=0;i<commodityHead.size();i++){
                    sheet.autoSizeColumn(i);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 设置excel标题
     * @param ebayHead
     * @param h_row
     * @param style
     */
    private static void setHead(List<String> ebayHead, HSSFRow h_row, HSSFCellStyle style) {

        h_row.setHeight((short)500);
        for(int c=0;c<ebayHead.size();c++){
            HSSFCell cell = h_row.createCell(c);
            cell.setCellValue(ebayHead.get(c));
            cell.setCellStyle(style);
        }
    }

    /**
     * 商品标题
     * @return
     */
    public static List<String> commodityHead(){
        List<String> head = new ArrayList<>();
        head.add("清关国家");
        head.add("清关海关编码");
        head.add("经审核的产品英文报关名称");
        head.add("关税税率（%）");
        head.add("VAT/GST税率（%）");
        head.add("需提供的监管证件/认证文件名称以英文逗号区分多个名称）");
        head.add("产品编码");
        head.add("报关海关编码");
        head.add("商家SKU");
        head.add("中文报关名称");
        head.add("英文报关名称");
        head.add("品牌类型");
        head.add("品牌");
        head.add("规格型号");
        head.add("材质");
        head.add("用途");
        head.add("产品图片");
        head.add("产品链接");
        head.add("备注");
        return head;
    }

    /**
     * 设置单元格样式
     * @param workbook
     * @param b
     * @return
     */
    public static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int b) {
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置cell样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //创建字体样式
        HSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");
        if(b ==1 || b == 3){
            font.setFontHeightInPoints((short) 11);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        }else {
            font.setFontHeightInPoints((short) 10);
        }
        if(b==1){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        style.setFont(font);
        return style;
    }

}
